<?php

namespace plugins\Excel\controller;

use app\common\controller\Common;
use think\Db;
use think\helper\Hash;

use app\family\model\TimRestApi as TimRestAPIModel;
use app\family\model\Teacher as TeacherModel;
require_once dirname(dirname(__FILE__)) . '/PHPExcel/PHPExcel.php';
require_once dirname(dirname(__FILE__)) . '/PHPExcel/PHPExcel/IOFactory.php';

/**
 * Excel控制器
 * @package plugins\Excel\controller
 */
class Excel extends Common
{
    /**
     * 导出Excel
     * @param string $expTitle 文件名称
     * @param array $expCellName 表头
     * @param array $expTableData 数据
     * @param array $mergeCells 合并单元格
     * @author HongPing <hongping626@qq.com>
     * @alter 蔡伟明 <314013107@qq.com>
     */
    public function export($expTitle = '', $expCellName = [], $expTableData = [], $mergeCells = [])
    {
        $expTitle == '' && $this->error('请填写文件名');
        empty($expCellName) && $this->error('请填写表头');

        ob_end_clean();
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $expTitle; //or $xlsTitle 文件名称可根据自己情况设定
        $cellNum  = count($expCellName);
        $dataNum  = count($expTableData);

        $objPHPExcel = new \PHPExcel();
        $cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ','EA','EB','EC','ED','EE','EF','EG','EH','EI','EJ','EK','EL','EM','EN','EO','EP','EQ','ER','ES','ET','EU','EV','EW','EX','EY','EZ','FA','FB','FC','FD','FE','FF','FG','FH','FI','FJ','FK','FL','FM','FN','FO','FP','FQ','FR','FS','FT','FU','FV','FW','FX','FY','FZ','GA','GB','GC','GD','GE','GF','GG','GH','GI','GJ','GK','GL','GM','GN','GO','GP','GQ','GR','GS','GT','GU','GV','GW','GX','GY','GZ','HA','HB','HC','HD','HE','HF','HG','HH','HI','HJ','HK','HL','HM','HN','HO','HP','HQ','HR','HS','HT','HU','HV','HW','HX','HY','HZ'];
        //合并单元格
        if (!empty($mergeCells)) {
            foreach ($mergeCells as $mergeCell) {
                $objPHPExcel->getActiveSheet(0)->mergeCells($mergeCell);
            }
        }

        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][2]);
            //根据内容设置单元格宽度
            $cellWidth = $expCellName[$i][1] == 'auto' ? strlen($expCellName[$i][2]) : $expCellName[$i][1];
            $objPHPExcel->getActiveSheet(0)->getColumnDimension($cellName[$i])->setWidth($cellWidth);
            // $objPHPExcel->getActiveSheet(0)->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][1]);
            if(isset($expCellName[$i][3]) && $expCellName[$i][3]=='FORMAT_NUMBER'){
                $objPHPExcel->getActiveSheet(0)->getStyle($cellName[$i])->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
            }
        }

        // Miscellaneous glyphs, UTF-8
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2),' ' . $expTableData[$i][$expCellName[$j][0]]);
            }
        }

        //下载
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0,max-age=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename='.$fileName.'.xlsx');
        header("Content-Transfer-Encoding:binary");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');		
        $objWriter->save('php://output');
		ob_clean();
        exit();
    }
    
    
    
    //导出csv模板数据
    public function export_csv($expTitle = '', $data = [], $list = []){       
       
        $file_name=$expTitle.".csv";
        header ( 'Content-Type: application/vnd.ms-excel' );
        header ( 'Content-Disposition: attachment;filename='.$file_name );
        header ( 'Cache-Control: max-age=0' );
        $file = fopen('php://output',"a");
        $limit=1000;
        $calc=0;
        foreach ($data as $v){
            $tit[]=iconv('UTF-8', 'GB2312//IGNORE',$v);
        }
        fputcsv($file,$tit);
        foreach ($list as $v){
            $calc++;
            if($limit==$calc){
                ob_flush();
                flush();
                $calc=0;
            }
            foreach ($v as $t){
                
                // 替换掉换行
                $t=preg_replace('/\s*/', '', $t);
                // 解决导出的数字会显示成科学计数法的问题
                $t=str_replace(',', "\t,", $t);
                // 转成gbk以兼容office乱码的问题
               // echo iconv('UTF-8','GBK',$v)."\t\r\n";
                
                $tarr[]=iconv('UTF-8', 'GB2312//IGNORE',$t);
            }
            fputcsv($file,$tarr);
            unset($tarr);
        }
        unset($list);
        fclose($file);
        exit();
    }

    /**
     * 导入Excel
     * @param string $file upload file $_FILES
     * @param null $table 导入到指定表，如果不指定，则返回拼接的数据，不执行导入
     * @param null $fields 字段名 只导入指定字段名的数据，excel表中其他在字段名找不到的，将不导入
     *         传入格式：array('id' => 'id', '用户名' => 'username')，key为数据库字段名，value为excel表头名
     * @param int $type 导入模式，0默认为增量导入（导入并跳过已存在的数据），1为覆盖导入（导入并覆盖已存在的数据）
     * @param null $where 查询依据，做配合查询使用
     * @param null $main_field 作为判断导入模式依据的主要字段，比如指定为KSH，则用KSH这个字段来判断是否已存在数据库
     * @author HongPing <hongping626@qq.com>
     * @alter 蔡伟明 <314013107@qq.com>
     * @return array
     */
    public function import($file, $table = null, $fields = null, $type = 0, $where = null, $main_field = null)
    {
        if(!file_exists($file)){
            return ["error" => 1, 'message' => '文件未找到!']; //file not found!
        }

        $objReader = \PHPExcel_IOFactory::createReader('Excel5'); //需要在前面加反斜杠，因为命名空间
        try{
            $PHPReader = $objReader->load($file);
        }catch(\Exception $e){}

        if(!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!

        $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
        $i = 0;
        $array = [];

        //循环读取每个工作表
        foreach($allWorksheets as $objWorksheet){
            $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
            $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
            $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数，excel以字母表示列数，比如B表示2列
            $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数，将字母表示的列数转换为数字
            $array[$i]["Title"] = $sheet_name;
            $array[$i]["Cols"]  = $allColumn;
            $array[$i]["Rows"]  = $allRow;
            $arr = [];
            $isMergeCell = [];
            foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
                foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                    $isMergeCell[$cellReference] = true;
                }
            }

            //循环读取每行数据
            for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
                $row = [];
                for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
                    $cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                    $afCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引，比如当前列为A，后一列则为B
                    $bfCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引，如果当前列为第一列，则前一列索引为@
                    $col     = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
                    $address = $col.$currentRow;//当前单元格的位置
                    $value   = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
                    $value   = trim($value); //去除首尾空格
                    //如果单元格内容为空，则跳过该单元格
                    // if ($value == '' || $value === NULL) {
                    //     continue;
                    // }
                    if(substr($value,0,1) == '='){//判断单元格的公式不完整的情况
                        return ["error" => 7, 'message' => '无法使用公式!']; //can not use the formula!
                    }

                    //判断单元格是否为数字类型
                    if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
                        // $cell_style_format=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                        $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
                        $format_code = $cell_style_format->getFormatCode();
                        if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                            $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
                        }else{
                            $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
                        }
                    }
                    //处理该单元格为合并单元格的情况
                    $temp = '';
                    if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
                        $temp = $value;
                    }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
                        $value = $arr[$currentRow-1][$currentColumn];
                    }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
                        $value = $temp;
                    }
                    $row[$currentColumn] = $value;
                }
                //判断一行中的全部单元格是否都有数据
                //如果不是，给出提示
                // if (count($row) != 0 && count($row) != $allColumn) {
                //     return ["error" => 4, 'message' => "工作表【{$sheet_name}】的第【{$currentRow}】行的数据不完整，请填写完整，并重新导入");
                // }

                $arr[$currentRow] = $row; //完整一行的数据
            }
            $array[$i]["Content"] = array_filter($arr);//过滤空行
            $i++;
        }

        unset($objWorksheet);
        unset($PHPReader);
        unset($PHPExcel);
        // unlink($file);

        // 没有指定表名，则直接返回拼接结果
        if ($table === null) {
            return ["error" => 0, "data" => $array];
        }
        if ($fields === null) {
            return ["error" => 2, 'message' => '未指定字段名!'];
        }
        if ($type != 0 && $type != 1) {
            return ["error" => 3, 'message' => '导入模式不正确!'];
        }
        if ($main_field === null) {
            return ["error" => 4, 'message' => '未指定用于判断依据的字段名!'];
        }
        if (!is_string($main_field)) {
            return ["error" => 5, 'message' => '用于判断依据的字段名只能为字符串类型!'];
        }

        //查询已经存在的数据，用于判断导入模式做对比
        $exists_list = Db::name($table)->where($where)->group($main_field)->column($main_field);

        //整理数据
        $fields    = array_flip($fields); //反转键值
        $data_list = [];
        $dataAdd   = [];
        $dataCover = [];
        $dataSkip  = [];
        foreach ($array as $key => $value) { //循环每一张工作表
            $firstRow = [];
            foreach ($value['Content'] as $row => $col) { //循环每一行数据
                $data = [];
                if ($row == 1) { //处理excel表的第一行，即表头，用来获取表头与字段名的关系
                    foreach ($col as $index => $val) { //循环每一个单元格
                        if (isset($fields[$val])) {
                            $firstRow[$index] = $fields[$val];
                        }
                    }
                } else { //这里开始是真正需要的数据
                    if (empty($firstRow)) {
                        return ["error" => 8, 'message' => '没有表头数据，无法导入!'];
                    }
                    foreach ($col as $index => $val) { //循环每一个单元格
                        if (isset($firstRow[$index])) {
                            $data[$firstRow[$index]] = trim($val);
                        }
                    }

                    // 判断导入模式
                    if ($type == 0) {//增量导入
                        if (in_array($data[$main_field], $exists_list)) {
                            $dataSkip['list'][] = $data[$main_field]; //记录跳过的数据
                            continue;//跳过已存在的考生
                        } else {
                            $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
                        }
                    } else {//覆盖导入
                        if (in_array($data[$main_field], $exists_list)) {
                            $dataCover['list'][] = $data[$main_field]; //记录覆盖的数据
                            $map[$main_field]    = $data[$main_field];
                            Db::name($table)->where($where)->where($map)->delete();//删除已存在的数据
                        } else {
                            $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
                        }
                    }

                    $data_list[] = $data;
                }
            }
        }

        if ($data_list) {
            if (Db::name($table)->insertAll($data_list)) {
                //计算新增、覆盖、跳过的数量
                $dataAdd['total']   = count($dataAdd['list']);
                $dataCover['total'] = isset($dataCover['list'])?count($dataCover['list']):0;
                $dataSkip['total']  = isset($dataSkip['list'])?count($dataSkip['list']):0;
                //将新增、覆盖、跳过的数据写入缓存
                cache('dataAdd', $dataAdd);
                cache('dataCover', $dataCover);
                cache('dataSkip', $dataSkip);
                cache('nextUrl', null);
                return ["error" => 0, 'message' => '成功导入 '. count($data_list). ' 条数据。'];
            } else {
                return ["error" => 9, 'message' => '导入失败!请重新导入。'];
            }
        } else {
            return ["error" => 10, 'message' => '上传的文件中，没有需要导入的数据!'];
        }
    }
	
	//加密密码
	public function setPasswordAttr($value)
    {
        return Hash::make((string)$value);
    }
	
	/*
	 * 获取省份ID
	 */
	public function getProvinceid($str){
		$r = DB::name("config_region")
			->where("fullname",$str)
			->find();
		if($r){
			return $r["id"];
		}else{
			return false;
		}
	}
	
	/*
	 * 获取城市ID
	 */
	public function getCityid($str,$parentid){
		$r = DB::name("config_region")
			->where("fullname",$str)
			->where("pid",$parentid)
			->find();
		if($r){
			return $r["id"];
		}else{
			return false;
		}
	}
	
	/*
	 * 获取学校ID
	 */
	public function getSchoolid($str,$parentid){
		$r = DB::name("school")
			->where("school_name",$str)
			->where("areaid",$parentid)
			->find();
		if($r){
			return $r["school_id"];
		}else{
			return false;
		}
	}

	/**
     * 扩展导入Excel
     * @param string $file upload file $_FILES
     * @param null $table 导入到指定表，如果不指定，则返回拼接的数据，不执行导入
     * @param null $fields 字段名 只导入指定字段名的数据，excel表中其他在字段名找不到的，将不导入
     *         传入格式：array('id' => 'id', '用户名' => 'username')，key为数据库字段名，value为excel表头名
     * @param int $type 导入模式，0默认为增量导入（导入并跳过已存在的数据），1为覆盖导入（导入并覆盖已存在的数据）
     * @param null $where 查询依据，做配合查询使用
     * @param null $main_field 作为判断导入模式依据的主要字段，比如指定为KSH，则用KSH这个字段来判断是否已存在数据库
     * @author HongPing <hongping626@qq.com>
     * @alter 蔡伟明 <314013107@qq.com>
     * @return array
     */
    public function importExt($file, $table = null, $fields = null, $type = 0, $where = null, $main_field = null)
    {
        if(!file_exists($file)){
            return ["error" => 1, 'message' => '文件未找到!']; //file not found!
        }

        $names=explode(".",$file);
        if(end($names)=='xls'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel5'); //03
        }elseif (end($names)=='xlsx'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); //07
        }else{
            return ["error" => 8, 'message' => '文件类型错误!'];
        }
        
        try{
            $PHPReader = $objReader->load($file);
        }catch(\Exception $e){}

        if(!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!

        $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
        $i = 0;
        $array = [];

        //循环读取每个工作表
        foreach($allWorksheets as $objWorksheet){
            $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
            $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
            $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数，excel以字母表示列数，比如B表示2列
            $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数，将字母表示的列数转换为数字
            $array[$i]["Title"] = $sheet_name;
            $array[$i]["Cols"]  = $allColumn;
            $array[$i]["Rows"]  = $allRow;
            $arr = [];
            $isMergeCell = [];
            foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
                foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                    $isMergeCell[$cellReference] = true;
                }
            }

            //循环读取每行数据
            for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
                $row = [];
                for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
                    $cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                    $afCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引，比如当前列为A，后一列则为B
                    $bfCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引，如果当前列为第一列，则前一列索引为@
                    $col     = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
                    $address = $col.$currentRow;//当前单元格的位置
                    $value   = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
                    $value   = trim($value); //去除首尾空格
                    //如果单元格内容为空，则跳过该单元格
                    // if ($value == '' || $value === NULL) {
                    //     continue;
                    // }
                    if(substr($value,0,1) == '='){//判断单元格的公式不完整的情况
                        return ["error" => 7, 'message' => '无法使用公式!']; //can not use the formula!
                    }

                    //判断单元格是否为数字类型
                    if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
                        // $cell_style_format=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                        $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
                        $format_code = $cell_style_format->getFormatCode();
                        if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                            $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
                        }else{
                            $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
                        }
                    }
                    //处理该单元格为合并单元格的情况
                    $temp = '';
                    if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
                        $temp = $value;
                    }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
                        $value = $arr[$currentRow-1][$currentColumn];
                    }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
                        $value = $temp;
                    }
                    $row[$currentColumn] = $value;
                }
                //判断一行中的全部单元格是否都有数据
                //如果不是，给出提示
                // if (count($row) != 0 && count($row) != $allColumn) {
                //     return ["error" => 4, 'message' => "工作表【{$sheet_name}】的第【{$currentRow}】行的数据不完整，请填写完整，并重新导入");
                // }

                $arr[$currentRow] = $row; //完整一行的数据
            }
            $array[$i]["Content"] = array_filter($arr);//过滤空行
            $i++;
        }

        unset($objWorksheet);
        unset($PHPReader);
        unset($PHPExcel);
        // unlink($file);

        // 没有指定表名，则直接返回拼接结果
        if ($table === null) {
            return ["error" => 0, "data" => $array];
        }
        if ($fields === null) {
            return ["error" => 2, 'message' => '未指定字段名!'];
        }
        if ($type != 0 && $type != 1) {
            return ["error" => 3, 'message' => '导入模式不正确!'];
        }
        if ($main_field === null) {
            return ["error" => 4, 'message' => '未指定用于判断依据的字段名!'];
        }
        if (!is_string($main_field)) {
            return ["error" => 5, 'message' => '用于判断依据的字段名只能为字符串类型!'];
        }

        //查询已经存在的数据，用于判断导入模式做对比
        $exists_list = Db::name($table)->where($where)->group($main_field)->column($main_field);

        //整理数据
        $fields    = array_flip($fields); //反转键值
        $data_list = [];
        $dataAdd   = [];
        $dataCover = [];
        $dataSkip  = [];
        $idcard='';//身份证重复数据
        $areaid=session('user_auth.areaid');
        foreach ($array as $key => $value) { //循环每一张工作表
            $firstRow = [];
            foreach ($value['Content'] as $row => $col) { //循环每一行数据
                $data = [];
                if ($row == 1) { //处理excel表的第一行，即表头，用来获取表头与字段名的关系
                    foreach ($col as $index => $val) { //循环每一个单元格
                        if (isset($fields[$val])) {
                            $firstRow[$index] = $fields[$val];
                        }
                    }
                } else { //这里开始是真正需要的数据
                    if (empty($firstRow)) {
                        return ["error" => 8, 'message' => '没有表头数据，无法导入!'];
                    }
                    
                    foreach ($col as $index => $val) { //循环每一个单元格
                        if (isset($firstRow[$index])) {
                            if($firstRow[$index]=="nickname"){
                                if(trim($val)==''){
                                    continue;
                                }else{
                                    $data[$firstRow[$index]] = trim($val);
                                }
                            }elseif($firstRow[$index]=="password"){
                        		//修改密码为加密后的密码
                        		$data[$firstRow[$index]] = $this->setPasswordAttr(trim($val));
//                      	}elseif($firstRow[$index]=="provinceid"){
//                      		//获取省份ID，并替换原来的字符串
//                      		$provinceid = $this->getProvinceid(trim($val));
//                      		$provinceid = isset($provinceid)?$provinceid:0;
//								$data[$firstRow[$index]] = $provinceid;
//                      	}elseif($firstRow[$index]=="cityid"){
//                      		//获取城市ID，并替换原来的字符串
//                      		$cityid = $this->getCityid(trim($val),$provinceid);
//                      		$cityid = isset($cityid)?$cityid:0;
//								$data[$firstRow[$index]] = $cityid;
//                      	}elseif($firstRow[$index]=="areaid"){
//                      		//获取区县ID，并替换原来的字符串
//                      		$areaid = $this->getCityid(trim($val),$cityid);
//                      		$areaid = isset($areaid)?$areaid:0;
//								$data[$firstRow[$index]] = $areaid;
                     	  }elseif($firstRow[$index]=="schoolid"){
                     		//获取为学校ID，并替换原来的字符串
                     		$schoolid = $this->getSchoolid(trim($val),$areaid);
                     		$schoolid = isset($schoolid)?$schoolid:0;
								$data[$firstRow[$index]] = $schoolid;
                        	}elseif($firstRow[$index]=="sex"){
                        	    $data[$firstRow[$index]] = $val=="女"?1:0;
                        	   
                        	}elseif($firstRow[$index]=="create_time"){
                        		//把时间替换成当前服务器时间戳
								$data[$firstRow[$index]] = time();
                        	}else{
                        		$data[$firstRow[$index]] = trim($val);
                        	}
                        	
                        }
                    }
                    $data['provinceid'] = session('user_auth.provinceid');
                    $data['cityid'] = session('user_auth.cityid');
                    $data['areaid'] = session('user_auth.areaid');
                    $data['password'] = $this->setPasswordAttr('abc123');
                    $data['create_time'] = time();
                   // var_dump($data[$main_field]);die;
                    // 判断导入模式
                    if ($type == 0) {//增量导入
                        if (in_array($data[$main_field], $exists_list)) {
                            $dataSkip['list'][] = $data[$main_field]; //记录跳过的数据
                            $idcard.=$data[$main_field]."-";
                            continue;//跳过已存在的考生
                        } else {
                            $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
                        }
                    } else {//覆盖导入
                        if (in_array($data[$main_field], $exists_list)) {
                            $dataCover['list'][] = $data[$main_field]; //记录覆盖的数据
                            $map[$main_field]    = $data[$main_field];
                            Db::name($table)->where($where)->where($map)->delete();//删除已存在的数据
                        } else {
                            $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
                        }
                    }

                    $data_list[] = $data;
                }
            }
        }
        
		//var_dump($a);
		//exit;
        $iderror='';
        if($idcard){
            $iderror="重复身份证号码:".$idcard;
        }
        if ($data_list) {
            foreach ($data_list as $kl=>$v){
                $r=Db::name($table)->insert($v);
                //更新登录名
                $userId = Db::name('user')->getLastInsID();
                $dateId=array();
                $dateId=array(
                    "id"=>$userId,
                    'username'=>"TEA".$userId,
                );
                Db::name($table)->update($dateId);
                //var_dump($r);die;
            }
            
            
            if ($r) {
                //计算新增、覆盖、跳过的数量
                $dataAdd['total']   = count($dataAdd['list']);
                $dataCover['total'] = isset($dataCover['list'])?count($dataCover['list']):0;
                $dataSkip['total']  = isset($dataSkip['list'])?count($dataSkip['list']):0;
                //将新增、覆盖、跳过的数据写入缓存
                cache('dataAdd', $dataAdd);
                cache('dataCover', $dataCover);
                cache('dataSkip', $dataSkip);
                cache('nextUrl', null);
               
                
                return ["error" => 0, 'message' => '成功导入 '. count($data_list). ' 条数据。'.$iderror];
            } else {
                return ["error" => 9, 'message' => '导入失败!请重新导入。'];
            }
        } else {
            return ["error" => 10, 'message' => '上传的文件中，没有需要导入的数据!'.$iderror];
        }
    }

    /**
     * 导出多工作表Excel
     * @param array $datas 数据，每个数组要包含'xls_name','xls_cell','data_list'
     * @param string $fileName 导出文件名
     * @author 蔡伟明 <314013107@qq.com>
     */
    public function exportMulti($datas = [], $fileName = '')
    {
        if (empty($datas)) {
            $this->error('找不到符合条件的数据');
        }

        $objPHPExcel = new \PHPExcel();
        $fileName = $fileName == '' ? date('Y-m-d') : $fileName;
        $cellName = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ','EA','EB','EC','ED','EE','EF','EG','EH','EI','EJ','EK','EL','EM','EN','EO','EP','EQ','ER','ES','ET','EU','EV','EW','EX','EY','EZ','FA','FB','FC','FD','FE','FF','FG','FH','FI','FJ','FK','FL','FM','FN','FO','FP','FQ','FR','FS','FT','FU','FV','FW','FX','FY','FZ','GA','GB','GC','GD','GE','GF','GG','GH','GI','GJ','GK','GL','GM','GN','GO','GP','GQ','GR','GS','GT','GU','GV','GW','GX','GY','GZ','HA','HB','HC','HD','HE','HF','HG','HH','HI','HJ','HK','HL','HM','HN','HO','HP','HQ','HR','HS','HT','HU','HV','HW','HX','HY','HZ'];

        //工作表序号
        $sheetIndex = 0;
        foreach ($datas as $key => $data) {
            // $xlsTitle = iconv('utf-8', 'gb2312', $data['xls_name']);//文件名称
            $xlsTitle = $data['xls_name'];//文件名称
            $cellNum  = count($data['xls_cell']);
            $dataNum  = count($data['data_list']);

            //如果当前工作表序号大于0，则创建新表
            if ($sheetIndex > 0) {
                $objPHPExcel->createSheet();
            }

            //设置表头
            for($i = 0; $i < $cellNum; $i++){
                //设置当前活动表，并设置值
                $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue($cellName[$i].'1', $data['xls_cell'][$i][2]);
                //得到当前活动的表
                $objActSheet = $objPHPExcel->getActiveSheet();
                // 给当前活动的表设置名称
                $objActSheet->setTitle($xlsTitle);
                //根据内容设置单元格宽度
                $cellWidth = $data['xls_cell'][$i][1] == 'auto' ? strlen($data['xls_cell'][$i][2]) : $data['xls_cell'][$i][1];
                $objActSheet->getColumnDimension($cellName[$i])->setWidth($cellWidth); //内容自适应
                //设置指定数据类型
                if($data['xls_cell'][$i][3] == 'FORMAT_NUMBER'){
                    $objActSheet->getStyle($cellName[$i])->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
                }
            }

            //设置具体内容
            for($i=0; $i < $dataNum; $i++){
                for($j=0; $j< $cellNum; $j++){
                    $objActSheet->setCellValue($cellName[$j].($i+2), $data['data_list'][$i][$data['xls_cell'][$j][0]]);
                }
            }
            $sheetIndex++;
        }

        //将当前活动表设置为第一个
        $objPHPExcel->setActiveSheetIndex(0);

        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename='.$fileName.'.xls');
        header("Content-Transfer-Encoding:binary");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        ob_end_clean();
        $objWriter->save('php://output');
        exit();
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    public function importChapter($file, $table = null, $fields = null, $listid = 0, $where = null, $main_field = null)
    {
        if(!file_exists($file)){
            return ["error" => 1, 'message' => '文件未找到!']; //file not found!
        }
    
        $names=explode(".",$file);
        if(end($names)=='xls'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel5'); //03
        }elseif (end($names)=='xlsx'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); //07
        }else{
            return ["error" => 8, 'message' => '文件类型错误!'];
        }
        try{
            $PHPReader = $objReader->load($file);
        }catch(\Exception $e){}
    
        if(!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!
    
        $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
        $i = 0;
        $array = [];
    
        //循环读取每个工作表
        foreach($allWorksheets as $objWorksheet){
            $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
            $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
            $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数，excel以字母表示列数，比如B表示2列
            $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数，将字母表示的列数转换为数字
            $array[$i]["Title"] = $sheet_name;
            $array[$i]["Cols"]  = $allColumn;
            $array[$i]["Rows"]  = $allRow;
            $arr = [];
            $isMergeCell = [];
            foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
                foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                    $isMergeCell[$cellReference] = true;
                }
            }
    
            //循环读取每行数据
            for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
                $row = [];
                for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
                    $cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                    $afCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引，比如当前列为A，后一列则为B
                    $bfCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引，如果当前列为第一列，则前一列索引为@
                    $col     = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
                    $address = $col.$currentRow;//当前单元格的位置
                    $value   = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
                    $value   = trim($value); //去除首尾空格
                    //如果单元格内容为空，则跳过该单元格
                    // if ($value == '' || $value === NULL) {
                    //     continue;
                    // }
                    if(substr($value,0,1) == '='){//判断单元格的公式不完整的情况
                        return ["error" => 7, 'message' => '无法使用公式!']; //can not use the formula!
                    }
    
                    //判断单元格是否为数字类型
                    if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
                        // $cell_style_format=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                        $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
                        $format_code = $cell_style_format->getFormatCode();
                        if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                            $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
                        }else{
                            $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
                        }
                    }
                    //处理该单元格为合并单元格的情况
                    $temp = '';
                    if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
                        $temp = $value;
                    }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
                        $value = $arr[$currentRow-1][$currentColumn];
                    }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
                        $value = $temp;
                    }
                    $row[$currentColumn] = $value;
                }
                //判断一行中的全部单元格是否都有数据
                //如果不是，给出提示
                // if (count($row) != 0 && count($row) != $allColumn) {
                //     return ["error" => 4, 'message' => "工作表【{$sheet_name}】的第【{$currentRow}】行的数据不完整，请填写完整，并重新导入");
                // }
    
                $arr[$currentRow] = $row; //完整一行的数据
            }
            $array[$i]["Content"] = array_filter($arr);//过滤空行
            $i++;
        }
    
        unset($objWorksheet);
        unset($PHPReader);
        unset($PHPExcel);
        // unlink($file);
        // 没有指定表名，则直接返回拼接结果
        if ($table === null) {
            return ["error" => 0, "data" => $array];
        }
        if ($fields === null) {
            return ["error" => 2, 'message' => '未指定字段名!'];
        }
        //整理数据
        $fields    = array_flip($fields); //反转键值
        $data_list = [];
        $dataAdd   = [];
        foreach ($array as $key => $value) { //循环每一张工作表
            $firstRow = [];
            foreach ($value['Content'] as $row => $col) { //循环每一行数据
                $data = [];
                if ($row == 1) { //处理excel表的第一行，即表头，用来获取表头与字段名的关系
                    foreach ($col as $index => $val) { //循环每一个单元格
                        if (isset($fields[$val])) {
                            $firstRow[$index] = $fields[$val];
                            $data[$firstRow[$index]] = trim($val);
                        }
                    }
                } else { //这里开始是真正需要的数据
                    if (empty($firstRow)) {
                        return ["error" => 8, 'message' => '没有表头数据，无法导入!'];
                    }
    
                    foreach ($col as $index => $val) { //循环每一个单元格
                        if (isset($firstRow[$index])) {
                            $data[$firstRow[$index]] = trim($val);
                        }
                        $data['listid']=$listid;
                        $data['create_time']=time();
                    }
                    $data_list[] = $data;
                }
        }
        
        if ($data_list) {
            foreach ($data_list as $kl=>$v){
                $r=Db::name($table)->insert($v);
                
            }
            if ($r) {
                return ["error" => 0, 'message' => '成功导入 '. count($data_list). ' 条数据。'];
            } else {
                return ["error" => 9, 'message' => '导入失败!请重新导入。'];
            }
        } else {
            return ["error" => 10, 'message' => '上传的文件中，没有需要导入的数据!'];
        }
    }
  }   
  
  
  
  
  
  
  
  
  
  
  
  public function importExam($file, $table = null, $fields = null, $listid = 0, $where = null, $main_field = null,$datag)
  {
      if(!file_exists($file)){
          return ["error" => 1, 'message' => '文件未找到!']; //file not found!
      }
  
      $names=explode(".",$file);
      if(end($names)=='xls'){
          $objReader = \PHPExcel_IOFactory::createReader('Excel5'); //03
      }elseif (end($names)=='xlsx'){
          $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); //07
      }else{
          return ["error" => 8, 'message' => '文件类型错误!'];
      }
      try{
          $PHPReader = $objReader->load($file);
      }catch(\Exception $e){}
  
      if(!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!
  
      $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
      $i = 0;
      $array = [];
  
      //循环读取每个工作表
      foreach($allWorksheets as $objWorksheet){
          $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
          $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
          $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数，excel以字母表示列数，比如B表示2列
          $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数，将字母表示的列数转换为数字
          $array[$i]["Title"] = $sheet_name;
          $array[$i]["Cols"]  = $allColumn;
          $array[$i]["Rows"]  = $allRow;
          $arr = [];
          $isMergeCell = [];
          foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
              foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                  $isMergeCell[$cellReference] = true;
              }
          }
  
          //循环读取每行数据
          for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
              $row = [];
              for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
                  $cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                  $afCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引，比如当前列为A，后一列则为B
                  $bfCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引，如果当前列为第一列，则前一列索引为@
                  $col     = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
                  $address = $col.$currentRow;//当前单元格的位置
                  $value   = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
                  $value   = trim($value); //去除首尾空格
                  //如果单元格内容为空，则跳过该单元格
                  // if ($value == '' || $value === NULL) {
                  //     continue;
                  // }
                  if(substr($value,0,1) == '='){//判断单元格的公式不完整的情况
                      return ["error" => 7, 'message' => '无法使用公式!']; //can not use the formula!
                  }
  
                  //判断单元格是否为数字类型
                  if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
                      // $cell_style_format=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                      $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
                      $format_code = $cell_style_format->getFormatCode();
                      if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                          $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
                      }else{
                          $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
                      }
                  }
                  //处理该单元格为合并单元格的情况
                  $temp = '';
                  if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
                      $temp = $value;
                  }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
                      $value = $arr[$currentRow-1][$currentColumn];
                  }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
                      $value = $temp;
                  }
                  $row[$currentColumn] = $value;
              }
              //判断一行中的全部单元格是否都有数据
              //如果不是，给出提示
              // if (count($row) != 0 && count($row) != $allColumn) {
              //     return ["error" => 4, 'message' => "工作表【{$sheet_name}】的第【{$currentRow}】行的数据不完整，请填写完整，并重新导入");
              // }
  
              $arr[$currentRow] = $row; //完整一行的数据
          }
          $array[$i]["Content"] = array_filter($arr);//过滤空行
          $i++;
      }
  
      unset($objWorksheet);
      unset($PHPReader);
      unset($PHPExcel);
      // unlink($file);
      // 没有指定表名，则直接返回拼接结果
      if ($table === null) {
          return ["error" => 0, "data" => $array];
      }
      if ($fields === null) {
          return ["error" => 2, 'message' => '未指定字段名!'];
      }
      //整理数据
      $fields    = array_flip($fields); //反转键值
      $data_list = [];
      $dataAdd   = [];
      foreach ($array as $key => $value) { //循环每一张工作表
          $firstRow = [];
          foreach ($value['Content'] as $row => $col) { //循环每一行数据
              $data = [];
              if ($row == 1) { //处理excel表的第一行，即表头，用来获取表头与字段名的关系
                  foreach ($col as $index => $val) { //循环每一个单元格
                      if (isset($fields[$val])) {
                          $firstRow[$index] = $fields[$val];
                          $data[$firstRow[$index]] = trim($val);
                      }
                  }
              } else { //这里开始是真正需要的数据
                  if (empty($firstRow)) {
                      return ["error" => 8, 'message' => '没有表头数据，无法导入!'];
                  }
  
                  foreach ($col as $index => $val) { //循环每一个单元格
                      if (isset($firstRow[$index])) {
                          $data[$firstRow[$index]] = trim($val);
                      }

                      $data['create_time']=time();
                      $data['adminid']=UID;
                      $data['language']=$datag['language'];
                      $data['grade']=$datag['grade'];
                      $data['subject']=$datag['subject'];
                      $data['term']=$datag['term'];
                      $data['arrangement']=$datag['arrangement'];
                      $data['version']=$datag['version'];
                  }
                  $data_list[] = $data;
              }
          }
  
          //var_dump($data_list);die;
          if ($data_list) {
              foreach ($data_list as $kl=>$v){
                  $r=Db::name($table)->insert($v);
  
              }
              if ($r) {
                  return ["error" => 0, 'message' => '成功导入 '. count($data_list). ' 条数据。'];
              } else {
                  return ["error" => 9, 'message' => '导入失败!请重新导入。'];
              }
          } else {
              return ["error" => 10, 'message' => '上传的文件中，没有需要导入的数据!'];
          }
      }
  }
  
  
  /**
   * 扩展导入Excel坊主
   */
  public function importFz($file, $table = null, $fields = null, $type = 0, $where = null, $main_field = null)
  {
      if(!file_exists($file)){
          return ["error" => 1, 'message' => '文件未找到!']; //file not found!
      }
  
      $names=explode(".",$file);
      if(end($names)=='xls'){
          $objReader = \PHPExcel_IOFactory::createReader('Excel5'); //03
      }elseif (end($names)=='xlsx'){
          $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); //07
      }else{
          return ["error" => 8, 'message' => '文件类型错误!'];
      }
  
      try{
          $PHPReader = $objReader->load($file);
      }catch(\Exception $e){}
  
      if(!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!
  
      $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
      $i = 0;
      $array = [];
  
      //循环读取每个工作表
      foreach($allWorksheets as $objWorksheet){
          $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
          $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
          $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数，excel以字母表示列数，比如B表示2列
          $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数，将字母表示的列数转换为数字
          $array[$i]["Title"] = $sheet_name;
          $array[$i]["Cols"]  = $allColumn;
          $array[$i]["Rows"]  = $allRow;
          $arr = [];
          $isMergeCell = [];
          foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
              foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                  $isMergeCell[$cellReference] = true;
              }
          }
  
          //循环读取每行数据
          for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
              $row = [];
              for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
                  $cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                  $afCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引，比如当前列为A，后一列则为B
                  $bfCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引，如果当前列为第一列，则前一列索引为@
                  $col     = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
                  $address = $col.$currentRow;//当前单元格的位置
                  $value   = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
                  $value   = trim($value); //去除首尾空格
                  //如果单元格内容为空，则跳过该单元格
                  // if ($value == '' || $value === NULL) {
                  //     continue;
                  // }
                  if(substr($value,0,1) == '='){//判断单元格的公式不完整的情况
                      return ["error" => 7, 'message' => '无法使用公式!']; //can not use the formula!
                  }
  
                  //判断单元格是否为数字类型
                  if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
                      // $cell_style_format=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                      $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
                      $format_code = $cell_style_format->getFormatCode();
                      if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                          $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
                      }else{
                          $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
                      }
                  }
                  //处理该单元格为合并单元格的情况
                  $temp = '';
                  if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
                      $temp = $value;
                  }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
                      $value = $arr[$currentRow-1][$currentColumn];
                  }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
                      $value = $temp;
                  }
                  $row[$currentColumn] = $value;
              }
              //判断一行中的全部单元格是否都有数据
              //如果不是，给出提示
              // if (count($row) != 0 && count($row) != $allColumn) {
              //     return ["error" => 4, 'message' => "工作表【{$sheet_name}】的第【{$currentRow}】行的数据不完整，请填写完整，并重新导入");
              // }
  
              $arr[$currentRow] = $row; //完整一行的数据
          }
          $array[$i]["Content"] = array_filter($arr);//过滤空行
          $i++;
      }
  
      unset($objWorksheet);
      unset($PHPReader);
      unset($PHPExcel);
      // unlink($file);
  
      // 没有指定表名，则直接返回拼接结果
      if ($table === null) {
          return ["error" => 0, "data" => $array];
      }
      if ($fields === null) {
          return ["error" => 2, 'message' => '未指定字段名!'];
      }
      if ($type != 0 && $type != 1) {
          return ["error" => 3, 'message' => '导入模式不正确!'];
      }
      if ($main_field === null) {
          return ["error" => 4, 'message' => '未指定用于判断依据的字段名!'];
      }
      if (!is_string($main_field)) {
          return ["error" => 5, 'message' => '用于判断依据的字段名只能为字符串类型!'];
      }
  
      //查询已经存在的数据，用于判断导入模式做对比
     // $exists_list = Db::name($table)->where($where)->group($main_field)->column($main_field);
  
      //整理数据
      $fields    = array_flip($fields); //反转键值
      $data_list = [];
      $dataAdd   = [];
      $dataCover = [];
      $dataSkip  = [];
      $idcard='';//身份证重复数据
      $areaid=session('user_auth.areaid');
      foreach ($array as $key => $value) { //循环每一张工作表
          $firstRow = [];
          foreach ($value['Content'] as $row => $col) { //循环每一行数据
              $data = [];
              if ($row == 1) { //处理excel表的第一行，即表头，用来获取表头与字段名的关系
                  foreach ($col as $index => $val) { //循环每一个单元格
                      if (isset($fields[$val])) {
                          $firstRow[$index] = $fields[$val];
                      }
                  }
              } else { //这里开始是真正需要的数据
                  if (empty($firstRow)) {
                      return ["error" => 8, 'message' => '没有表头数据，无法导入!'];
                  }
  
                  foreach ($col as $index => $val) { //循环每一个单元格
                      if (isset($firstRow[$index])) {
                          if($firstRow[$index]=="nickname"){
                              if(trim($val)==''){
                                  continue;
                              }else{
                                  $data[$firstRow[$index]] = trim($val);
                              }
                          }elseif($firstRow[$index]=="sex"){
                       	      $data[$firstRow[$index]] = $val=="女"?1:0;
  
                       	  }else{
                       	      $data[$firstRow[$index]] = trim($val);
                       	  }
                       	   
                      }
                  }
                  $data['provinceid'] = session('user_auth.provinceid');
                  $data['cityid'] = session('user_auth.cityid');
                  $data['areaid'] = session('user_auth.areaid');
                  $data['role'] = 14;
                  $data['password'] = $this->setPasswordAttr('abc123');
                  $data['create_time'] = time();
                  // var_dump($data[$main_field]);die;
                  // 判断导入模式
                 $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
  
                  $data_list[] = $data;
              }
          }
      }
  
      //var_dump($a);
      //exit;
      $iderror='';
      if($idcard){
          $iderror="重复身份证号码:".$idcard;
      }
      //获取所在县的最后管理员数量
      $nums = Db::name('config_region')->find(session('user_auth.areaid'));//session('user_auth.areaid')
      if(!$nums){
          return ["error" => 9, 'message' => '不是县级管理员'];
      }
      $num=(int)$nums['nums']+1;
      if ($data_list) {
          foreach ($data_list as $kl=>$v){
              $r=Db::name($table)->insert($v);
            
              //更新登录名
              $userId = Db::name('admin_user')->getLastInsID();
              $dateId=array();
              
              $allnum=$num+$kl;
              
              $dateId=array(
                  "id"=>$userId,
                  'username'=>session('user_auth.areaid').str_pad($allnum,4,"0",STR_PAD_LEFT),
              );
              Db::name($table)->update($dateId);
              
              
          }
          
          $dataNum=array(
              "id"=>session('user_auth.areaid'),
              'nums'=>$allnum,
          );
          Db::name('config_region')->update($dateId);
  
          if ($r) {
              //计算新增、覆盖、跳过的数量
              $dataAdd['total']   = count($dataAdd['list']);
              $dataCover['total'] = isset($dataCover['list'])?count($dataCover['list']):0;
              $dataSkip['total']  = isset($dataSkip['list'])?count($dataSkip['list']):0;
              //将新增、覆盖、跳过的数据写入缓存
              cache('dataAdd', $dataAdd);
              cache('dataCover', $dataCover);
              cache('dataSkip', $dataSkip);
              cache('nextUrl', null);
               
  
              return ["error" => 0, 'message' => '成功导入 '. count($data_list). ' 条数据。'.$iderror];
          } else {
              return ["error" => 9, 'message' => '导入失败!请重新导入。'];
          }
      } else {
          return ["error" => 10, 'message' => '上传的文件中，没有需要导入的数据!'.$iderror];
      }
  }


    //家校通学员导入
  public function family_student($file, $table = null, $fields = null, $type = 0, $where = null, $main_field = null,$school_id,$grade_id,$class_id)
  {

      $count = db('school_trainee')->where('class_id',$class_id)->count('id');
      if($count > 100){
          $this->error("班级学生已满100个，停止添加学生");
      }
      if(!file_exists($file)){
          return ["error" => 1, 'message' => '文件未找到!']; //file not found!
      }

      $names=explode(".",$file);
      if(end($names)=='xls'){
          $objReader = \PHPExcel_IOFactory::createReader('Excel5'); //03
      }elseif (end($names)=='xlsx'){
          $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); //07
      }else{
          return ["error" => 8, 'message' => '文件类型错误!'];
      }

      try{
          $PHPReader = $objReader->load($file);
      }catch(\Exception $e){}

      if(!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!

      $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
      $i = 0;
      $array = [];

      //循环读取每个工作表
      foreach($allWorksheets as $objWorksheet){
          $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
          $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
          $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数，excel以字母表示列数，比如B表示2列
          $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数，将字母表示的列数转换为数字
          $array[$i]["Title"] = $sheet_name;
          $array[$i]["Cols"]  = $allColumn;
          $array[$i]["Rows"]  = $allRow;
          $arr = [];
          $isMergeCell = [];
          foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
              foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
                  $isMergeCell[$cellReference] = true;
              }
          }

          //循环读取每行数据
          for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
              $row = [];
              for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
                  $cell    = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
                  $afCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引，比如当前列为A，后一列则为B
                  $bfCol   = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引，如果当前列为第一列，则前一列索引为@
                  $col     = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
                  $address = $col.$currentRow;//当前单元格的位置
                  $value   = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
                  $value   = trim($value); //去除首尾空格
                  //如果单元格内容为空，则跳过该单元格
                  // if ($value == '' || $value === NULL) {
                  //     continue;
                  // }
                  if(substr($value,0,1) == '='){//判断单元格的公式不完整的情况
                      return ["error" => 7, 'message' => '无法使用公式!']; //can not use the formula!
                  }

                  //判断单元格是否为数字类型
                  if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
                      // $cell_style_format=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
                      $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
                      $format_code = $cell_style_format->getFormatCode();
                      if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
                          $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
                      }else{
                          $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
                      }
                  }
                  //处理该单元格为合并单元格的情况
                  $temp = '';
                  if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
                      $temp = $value;
                  }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
                      $value = $arr[$currentRow-1][$currentColumn];
                  }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
                      $value = $temp;
                  }
                  $row[$currentColumn] = $value;
              }
              //判断一行中的全部单元格是否都有数据
              //如果不是，给出提示
              // if (count($row) != 0 && count($row) != $allColumn) {
              //     return ["error" => 4, 'message' => "工作表【{$sheet_name}】的第【{$currentRow}】行的数据不完整，请填写完整，并重新导入");
              // }

              $arr[$currentRow] = $row; //完整一行的数据
          }
          $array[$i]["Content"] = array_filter($arr);//过滤空行
          $i++;
      }

      unset($objWorksheet);
      unset($PHPReader);
      unset($PHPExcel);
      // unlink($file);

      // 没有指定表名，则直接返回拼接结果
      if ($table === null) {
          return ["error" => 0, "data" => $array];
      }
      if ($fields === null) {
          return ["error" => 2, 'message' => '未指定字段名!'];
      }
      if ($type != 0 && $type != 1) {
          return ["error" => 3, 'message' => '导入模式不正确!'];
      }
      if ($main_field === null) {
          return ["error" => 4, 'message' => '未指定用于判断依据的字段名!'];
      }
      if (!is_string($main_field)) {
          return ["error" => 5, 'message' => '用于判断依据的字段名只能为字符串类型!'];
      }

      //查询已经存在的数据，用于判断导入模式做对比
      // $exists_list = Db::name($table)->where($where)->group($main_field)->column($main_field);

      //整理数据
      $fields    = array_flip($fields); //反转键值
      $data_list = [];
      $dataAdd   = [];
      $dataCover = [];
      $dataSkip  = [];
      $idcard='';//身份证重复数据
      $areaid=session('user_auth.areaid');
      foreach ($array as $key => $value) { //循环每一张工作表
          $firstRow = [];
          foreach ($value['Content'] as $row => $col) { //循环每一行数据
              $data = [];
              if ($row == 1) { //处理excel表的第一行，即表头，用来获取表头与字段名的关系
                  foreach ($col as $index => $val) { //循环每一个单元格
                      if (isset($fields[$val])) {
                          $firstRow[$index] = $fields[$val];
                      }
                  }
              } else { //这里开始是真正需要的数据
                  if (empty($firstRow)) {
                      return ["error" => 8, 'message' => '没有表头数据，无法导入!'];
                  }

                  foreach ($col as $index => $val) { //循环每一个单元格
                      if (isset($firstRow[$index])) {
                          if($firstRow[$index]=="trainee_name"){
                              if(trim($val)==''){
                                  return ["error" => 9, 'message' => '学生姓名必填'];
                              }else{
                                  $data[$firstRow[$index]] = trim($val);
                              }
                          }elseif($firstRow[$index]=="trainee_number"){
                              if(trim($val)==''){
                                  return ["error" => 9, 'message' => '学生学籍号必填'];
                              }else{
                                  $data[$firstRow[$index]] = trim($val);
                              }
                          }elseif($firstRow[$index]=="fa_mobile"){
                              $data[$firstRow[$index]] = trim($val);
                          }else{
                              $data[$firstRow[$index]] = trim($val);
                          }

                      }
                  }
                  $data['school_id'] = $school_id;
                  $data['grade_id'] = $grade_id;
                  $data['class_id'] = $class_id;
                  $data['create_time'] = time();
                  // var_dump($data[$main_field]);die;
                  // 判断导入模式
                  $dataAdd['list'][] = $data[$main_field]; //记录新增的数据

                  $data_list[] = $data;
              }
          }
      }

      $iderror='';
      $time=time();
      if ($data_list) {
          foreach ($data_list as $kl=>$v){
              $count = db('school_trainee')->where('class_id',$class_id)->count('id');
              if($count > 100){
                  $this->error("班级学生已满100个，停止添加学生");
              }
              $trainee['trainee_name'] = trim($v['trainee_name']);
              $trainee['mobile'] = trim($v['mobile']);
              $oldchar=array(" ","　","\t","\n","\r");
              $newchar=array("","","","","");
              $trainee['trainee_number'] = str_replace($oldchar,$newchar,$v['trainee_number']);
              if(!preg_match("/^[LG][0-9]{17}[xX0-9]$/",$trainee['trainee_number'])){
                  $iderror .= $trainee['trainee_name']."学籍号不合法";
                  $r = 1;
                  continue;
              }
              $trainee['class_number'] = $v['class_number'];
              $trainee['school_id'] = $v['school_id'];
              $trainee['grade_id'] = $v['grade_id'];
              $trainee['class_id'] = $v['class_id'];
              $trainee['address'] = $v['address'];
              $trainee['create_time'] = $time;
              $issetor = db('school_trainee')->where('trainee_number',$trainee['trainee_number'])->find();
              if($issetor){
                  if($issetor['grade_id'] == 0){
                      $r=Db::name($table)->where('trainee_number',$trainee['trainee_number'])->update($trainee);
                      db('school_parent_relation')->where('trainee_number',$trainee['trainee_number'])->update(['schoolid'=>$school_id,'classid'=>$class_id]);
                      $TimRestAPI=new TimRestAPIModel();

                      //加入学习群
                      $jiazhang = db('school_parent_relation')->where('trainee_number',$trainee['trainee_number'])->field('openid,relation_name,realname')->select();
                      if($jiazhang){
                          foreach($jiazhang as $k1=>$v1){
                              if(!empty($v1['openid'])){
                                  $group_id=$class_id;
                                  $member_id=$v1['openid'];
                                  $silence=1;
                                  $rs=$TimRestAPI->group_add_group_member($group_id, $member_id, $silence);
                                  //修改群名片
                                  $TimRestAPI->group_modify_group_member_info2($group_id, $member_id, "Member",null,0,$issetor['trainee_name'].$v1['relation_name']."-".$v1['realname']);
                              }
                          }
                      }

                      $issetor_family = db('school_parent_relation')->where('trainee_number',$trainee['trainee_number'])->where('mobile',$v['fa_mobile'])->find();
                      if(!$issetor_family){
                          if(!$v['realname']){
                              continue;
                          }
                          $par['realname'] = trim($v['realname']);
                          $par['relation_name'] = trim($v['relation_name']);
                          $par['mobile'] = trim($v['fa_mobile']);
                          $par['schoolid'] = $v['school_id'];
                          $par['classid'] = $v['class_id'];
                          $par['trainee_id'] = $issetor['id'];
                          $par['trainee_number'] = trim($v['trainee_number']);
                          $par['create_time'] = $time;
                          //控制器与方法名
                          $controller=request()->controller();
                          $action_name =request()->action();
                          $par['action'] = "$controller/$action_name";
                          db('school_parent_relation')->insert($par);
                      }
                  }else{
                      $r = 1;
                      $iderror .= $issetor['trainee_name']."已存在另一班级--";
                  }
              }else{
                  $r=Db::name($table)->insert($trainee);
                  $tr_id = Db::name($table)->getLastInsID();
                  if($tr_id){
                      if(!$v['realname']){
                          continue;
                      }
                      $par['realname'] = trim($v['realname']);
                      $par['relation_name'] = trim($v['relation_name']);
                      $par['mobile'] = trim($v['fa_mobile']);
                      $par['schoolid'] = $v['school_id'];
                      $par['classid'] = $v['class_id'];
                      $par['trainee_id'] = $tr_id;
                      $par['trainee_number'] = trim($v['trainee_number']);
                      $par['create_time'] = $time;
                      //控制器与方法名
                      $controller=request()->controller();
                      $action_name =request()->action();
                      $par['action'] = "$controller/$action_name";
                  }
                  db('school_parent_relation')->insert($par);
              }
            }
          if ($r) {

              return ["error" => 0, 'message' => '成功导入。'.$iderror];
          } else {
              return ["error" => 9, 'message' => '导入失败!请重新导入。'];
          }
      } else {
          return ["error" => 10, 'message' => '上传的文件中，没有需要导入的数据!'.$iderror];
      }
  }

    /**
     * 扩展导入學生成績
     */
    public function family_record($file, $table = null, $fields = null, $type = 0, $where = null, $main_field = null,$class_id,$re_id)
    {
        if (!file_exists($file)) {
            return ["error" => 1, 'message' => '文件未找到!']; //file not found!
        }

        $names = explode(".", $file);
        ini_set('max_execution_time', '0');
       // setlocale(LC_ALL, 'zh_CN');
        if (end($names) == 'csv') {
            $objReader = \PHPExcel_IOFactory::createReader('CSV'); //03
            $objReader->setInputEncoding('GBK');
            $objReader->setDelimiter(',');
        } else {
            return ["error" => 8, 'message' => '文件类型错误!'];
        }

        try {
            $PHPReader = $objReader->load($file);
        } catch (\Exception $e) {
        }

        if (!isset($PHPReader)) return ["error" => 6, 'message' => '读取错误!']; //read error!

        
        
        
        
        $sheet = $PHPReader->getSheet(0);
        // 取得总行数
        $highestRow = $sheet->getHighestRow();
        // 取得总列数
        $highestColumn = $sheet->getHighestColumn();
        //循环读取excel文件,读取一条,插入一条
        $data=array();
        //从第一行开始读取数据
        for($j=1;$j<=$highestRow;$j++){
            //从A列读取数据
            for($k='A';$k<=$highestColumn;$k++){
                // 读取单元格
                $data[$j][]=$tit[]=$PHPReader->getActiveSheet()->getCell("$k$j")->getValue();
            }
        }
       
        
        
        
        
      

        //查询已经存在的数据，用于判断导入模式做对比
        // $exists_list = Db::name($table)->where($where)->group($main_field)->column($main_field);

        //整理数据
        //循环每一张工作表

        $record = [];
        $stu_id = [];

        $k=2;//学科数据
        $allColumn=count($data['1']);
        //学生id
        foreach ($data as $kb => $vb) {
            if ($kb != 1 && !empty($vb[0])) {
                $stu_id[$kb]['trainee_number']=$vb[0] ;
                $subject=array();
                for($i=$k;$i<$allColumn;$i++){
                    $subject[$i]['name']=$data[1][$i];
                    $subject[$i]['scorse']=$vb[$i];
                }
                $stu_id[$kb] ['subject']=$subject ;

            }
        }
        //dump($stu_id);die;
        foreach($stu_id as $kc=>$vc){
           foreach($vc['subject'] as $kd=>$vd){
               $data = array(
                   'trainee_number' => $vc['trainee_number'],
                   'object_name'=> $vd['name'],
                   'scores'=> $vd['scorse'],
                   'class_id'=> $class_id,
                   'recorde_id'=> $re_id,
                   'create_time'=>time()
               );
               $r = db($table)->insert($data);
           }
        }

    }
  
  
  
  
    
}